/*
 *接口编写处...
*如果接口需要做Action的权限验证，请在Action上使用属性
*如: [ApiActionPermission("big_data_actual_sales",Enums.ActionPermissionOptions.Search)]
 */
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.AspNetCore.Http;
using VOL.Entity.DomainModels;
using VOL.JIRUI.IServices;
using Microsoft.AspNetCore.Authorization;
using VOL.Core.Extensions;
using VOL.Core.DBManager;
using System.Linq;
using StackExchange.Redis;
using OfficeOpenXml.Style;
using System.Globalization;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Math;
using VOL.Core.Filters;

namespace VOL.JIRUI.Controllers
{
    public partial class big_data_actual_salesController
    {
        private readonly Ibig_data_actual_salesService _service;//访问业务代码
        private readonly IHttpContextAccessor _httpContextAccessor;

        [ActivatorUtilitiesConstructor]
        public big_data_actual_salesController(
            Ibig_data_actual_salesService service,
            IHttpContextAccessor httpContextAccessor
        )
        : base(service)
        {
            _service = service;
            _httpContextAccessor = httpContextAccessor;
        }
        /// <summary>
        /// 发货实销信息  增量 根据big_data_basic表的时间节点只要有空的 通过vin查找并将日期更新到基础表及发货实销表 
        /// vin码有重复的，需过滤
        /// 通过基础表的vin去DMS查找发货、实销数据，通过拿到的发货、实销数据再去查本地的发货实销数据：
        /// 如果有重复的就更新，没有就添加，将查找的值再更新到基础表
        /// receiving_order_time 接单日期（提报时间）
        /// financial_verification_time 财务核价时间
        /// departure_time 二次发车时间（发货时间）
        /// </summary>
        /// <returns></returns>
        ///[ApiTask]
        [HttpPost, HttpGet, Route("getSalesDateInfo"), AllowAnonymous]
        public object getSalesDateInfo()
        {
            string sql = " SELECT vin FROM big_data_basic bdb WHERE receiving_order_time is null or financial_verification_time is null or departure_time is null ";
            List<string> listStr = DBServerProvider.SqlDapper.QueryList<string>(sql, null).ToList();
            listStr = listStr.Distinct().ToList();
            string sqlstart = "SELECT vin,vehicle_model,deliver_no,dms_vehicle_match_order_no,sap_voucher_time,financial_time,order_time,create_time,phone,owner_name,construction_date,purchased_date,purchased_price,jssys FROM( SELECT  * FROM  ( SELECT b.* FROM ( SELECT a.*, ROW_NUMBER() OVER (PARTITION BY a.vin ORDER BY a.CREATE_TIME desc )rw  from ( SELECT t1.vin, t1.vehicle_model,t1.deliver_no, t1.dms_vehicle_match_order_no, TO_DATE(t1.sap_voucher_time,'yyyyMMdd') as sap_voucher_time,      T2.UPDATE_DATE financial_time,  T2.order_Date order_time , t1.create_time   FROM TT_Q_VEHICLE_DISPATCH t1,TT_SALES_ORDER T2  WHERE t1.IS_DELETE = 0 AND T1.DMS_VEHICLE_MATCH_ORDER_NO=T2.ORDER_NO(+)  AND T1.DISPATCH_MODEL<>'82321003'  ORDER BY t1.CREATE_TIME  )a  )b WHERE b.rw = 1  )h LEFT JOIN ( SELECT m.* FROM (  SELECT n.*, ROW_NUMBER() OVER (PARTITION BY n.vin2 ORDER BY n.PURCHASED_DATE desc)rw FROM ( SELECT A.vin vin2,  B.phone, B.owner_name,  B.CREATE_DATE construction_date,  A.PURCHASED_DATE purchased_date, (D.ORDER_AMOUNT / D.ORDER_QUANTITY) purchased_price,        F.jssys,        B.CREATE_DATE   FROM TM_SV_VEHICLE A, TM_SV_OWNER B, MATCH_VIEW C, TT_SALES_ORDER D,  FETURE_INFO_VIEW F  WHERE A.VEHICLE_ID = B.VEHICLE_ID AND A.VIN = C.VIN AND D.DERIVE_MATERIAL_CODE = F.mc AND C.order_id = D.ORDER_ID AND A.CAR_STATUS = 10641006    AND A.IS_DELETE = 0  ORDER BY A.PURCHASED_DATE DESC  )n )m WHERE m.rw = 1 )k ON  h.vin = k.vin2 ) where ";
            string sqlfinal = "";
            string sqlparams = "";
            if (listStr!=null&&listStr.Count>0)
            {
                int total = listStr.Count;
                int rows = 500;
                int pageSize = (total % rows) + 1;
                int current = 1;
                
                for (int j = 0; j < listStr.Count; j++)
                {
                    sqlparams = sqlparams + "'" + listStr[j] + "',";
                    if ((j==current*rows-1)||(current==pageSize&&j==(total-1))) //执行一次数据库操作
                    {
                        current++;
                        sqlparams = sqlparams.TrimEnd(',');
                        sqlfinal = sqlstart + " vin in (" + sqlparams + ") ";
                        sqlfinal = sqlfinal.Replace("'", "''");//单引号转义
                        List<big_data_actual_sales> listActual = DBServerProvider.SqlDapper.QueryList<big_data_actual_sales>("select * from openquery(DMS,'" + sqlfinal + "')", "").ToList();
                        sqlparams = "";
                        foreach (big_data_actual_sales item in listActual)
                        {
                            big_data_actual_sales actual_Sales = DBServerProvider.DbContext.Set<big_data_actual_sales>().Where(w => w.vin.Equals(item.vin)).ToList().LastOrDefault();
                            if (actual_Sales != null)//更新
                            {
                                List<big_data_basic> listBasic = DBServerProvider.DbContext.Set<big_data_basic>().Where(w => w.vin.Equals(item.vin)).ToList();
                                if (listBasic != null && listBasic.Count > 0)
                                {
                                    for (int k = 0; k < listBasic.Count; k++)
                                    {
                                        big_data_basic basic = listBasic[k];
                                        basic.financial_verification_time = item.financial_time;
                                        basic.receiving_order_time = item.order_time;
                                        basic.departure_time = item.sap_voucher_time;
                                        basic.status = 0; //1已推送 0未推送
                                        DBServerProvider.DbContext.Update(basic);
                                    }
                                }
                                actual_Sales.vehicle_model = item.vehicle_model;
                                actual_Sales.deliver_no = item.deliver_no;
                                actual_Sales.dms_vehicle_match_order_no = item.dms_vehicle_match_order_no;
                                actual_Sales.sap_voucher_time = item.sap_voucher_time;
                                actual_Sales.financial_time = item.financial_time;
                                actual_Sales.order_time = item.order_time;
                                actual_Sales.phone = item.phone;
                                actual_Sales.construction_date = item.construction_date;//建档日期
                                actual_Sales.owner_name = item.owner_name;
                                actual_Sales.purchased_date = item.purchased_date;//实销时间
                                actual_Sales.purchased_price = item.purchased_price;
                                actual_Sales.jssys = item.jssys;
                                DBServerProvider.DbContext.Update(actual_Sales);

                            }
                            else//添加
                            {
                                List<big_data_basic> listBasic = DBServerProvider.DbContext.Set<big_data_basic>().Where(w => w.vin.Equals(item.vin)).ToList();
                                if (listBasic != null && listBasic.Count > 0)
                                {
                                    for (int k = 0; k < listBasic.Count; k++)
                                    {
                                        big_data_basic basic = listBasic[k];
                                        basic.financial_verification_time = item.financial_time;
                                        basic.receiving_order_time = item.order_time;
                                        basic.departure_time = item.sap_voucher_time;
                                        basic.status = 0; //1已推送 0未推送
                                        DBServerProvider.DbContext.Update(basic);
                                    }
                                }
                                big_data_actual_sales actual_Sales_add = new big_data_actual_sales();
                                actual_Sales_add.big_data_actual_sales_id = Guid.NewGuid();
                                actual_Sales_add.vin = item.vin;
                                actual_Sales_add.vehicle_model = item.vehicle_model;
                                actual_Sales_add.Creator = "DMS";
                                actual_Sales_add.CreateDate = DateTime.Now;
                                actual_Sales_add.status = 0;
                                actual_Sales_add.deliver_no = item.deliver_no;
                                actual_Sales_add.dms_vehicle_match_order_no = item.dms_vehicle_match_order_no;
                                actual_Sales_add.sap_voucher_time = item.sap_voucher_time;
                                actual_Sales_add.financial_time = item.financial_time;
                                actual_Sales_add.order_time = item.order_time;
                                actual_Sales_add.phone = item.phone;
                                actual_Sales_add.construction_date = item.construction_date;//建档日期
                                actual_Sales_add.owner_name = item.owner_name;
                                actual_Sales_add.purchased_date = item.purchased_date;//实销时间
                                actual_Sales_add.purchased_price = item.purchased_price;
                                actual_Sales_add.jssys = item.jssys;
                                DBServerProvider.DbContext.Add(actual_Sales_add);
                            }
                        }
                        DBServerProvider.DbContext.SaveChanges();
                    }
                }
            }
            DBServerProvider.DbContext.SaveChanges();
            return listStr.Count;
        }
        /// <summary>
        /// 发货实销信息 首次全部获取
        /// </summary>
        /// <returns></returns>.
        ///[ApiTask]
        /// ********************全量 全量  慎重使用********************
        [HttpPost, HttpGet, Route("getActualSalesInfo"), AllowAnonymous]
        public object getActualSalesInfo()
        {
            //发货信息
            string sql = " SELECT vin,vehicle_model,deliver_no,dms_vehicle_match_order_no,sap_voucher_time,financial_time,order_time,create_time,phone,owner_name,construction_date,purchased_date,purchased_price,jssys FROM( SELECT  * FROM  ( SELECT b.* FROM ( SELECT a.*, ROW_NUMBER() OVER (PARTITION BY a.vin ORDER BY a.CREATE_TIME desc )rw  from ( SELECT t1.vin, t1.vehicle_model,t1.deliver_no, t1.dms_vehicle_match_order_no, TO_DATE(t1.sap_voucher_time,'yyyyMMdd') as sap_voucher_time,      T2.UPDATE_DATE financial_time,  T2.order_Date order_time , t1.create_time   FROM TT_Q_VEHICLE_DISPATCH t1,TT_SALES_ORDER T2  WHERE t1.IS_DELETE = 0 AND T1.DMS_VEHICLE_MATCH_ORDER_NO=T2.ORDER_NO(+)  AND T1.DISPATCH_MODEL<>'82321003'  ORDER BY t1.CREATE_TIME  )a  )b WHERE b.rw = 1  )h LEFT JOIN ( SELECT m.* FROM (  SELECT n.*, ROW_NUMBER() OVER (PARTITION BY n.vin2 ORDER BY n.PURCHASED_DATE desc)rw FROM ( SELECT A.vin vin2,  B.phone, B.owner_name,  B.CREATE_DATE construction_date,  A.PURCHASED_DATE purchased_date, (D.ORDER_AMOUNT / D.ORDER_QUANTITY) purchased_price,        F.jssys,        B.CREATE_DATE   FROM TM_SV_VEHICLE A, TM_SV_OWNER B, MATCH_VIEW C, TT_SALES_ORDER D,  FETURE_INFO_VIEW F  WHERE A.VEHICLE_ID = B.VEHICLE_ID AND A.VIN = C.VIN AND D.DERIVE_MATERIAL_CODE = F.mc AND C.order_id = D.ORDER_ID AND A.CAR_STATUS = 10641006    AND A.IS_DELETE = 0  ORDER BY A.PURCHASED_DATE DESC  )n )m WHERE m.rw = 1 )k ON  h.vin = k.vin2 ) ";
            sql = sql.Replace("'", "''");//单引号转义
            List<big_data_actual_sales> list = DBServerProvider.SqlDapper.QueryList<big_data_actual_sales>("select * from openquery(DMS,'" + sql + "')", "").ToList();
            if (list!=null&&list.Count>0)
            {
                for (int i = 0; i < list.Count; i++)
                {
                    big_data_actual_sales actual_Sales = new big_data_actual_sales();
                    actual_Sales.big_data_actual_sales_id = Guid.NewGuid();
                    actual_Sales.vin = list[i].vin;
                    actual_Sales.vehicle_model = list[i].vehicle_model;
                    actual_Sales.Creator = "DMS";
                    actual_Sales.CreateDate = DateTime.Now;
                    actual_Sales.status = 0;
                    actual_Sales.deliver_no = list[i].deliver_no;
                    actual_Sales.dms_vehicle_match_order_no = list[i].dms_vehicle_match_order_no;
                    actual_Sales.sap_voucher_time = list[i].sap_voucher_time;
                    actual_Sales.financial_time = list[i].financial_time;
                    actual_Sales.order_time = list[i].order_time;   
                    actual_Sales.phone = list[i].phone;
                    actual_Sales.construction_date = list[i].construction_date;//建档日期
                    actual_Sales.owner_name = list[i].owner_name;
                    actual_Sales.purchased_date = list[i].purchased_date;//实销时间
                    actual_Sales.purchased_price = list[i].purchased_price;
                    actual_Sales.jssys = list[i].jssys;  
                    DBServerProvider.DbContext.Add(actual_Sales);
                }
            }
            DBServerProvider.DbContext.SaveChanges();
            return list.Count;
        }
    }
}
